<?php

/**
 * 新版本,每个咨询类型. 单独定价.
 */

namespace app\v6\controller;

use app\models\Treatise;
use app\service\TreatiseService;
use think\Db;
use think\facade\Lang;
use think\facade\Log;
use think\facade\Cache;

class Symptom
{
    // 获取所有症状内容
    public function getSymptoms()
    {
        // 缓存键名
        $cacheKey = 'symptoms_data_v1';

        try {
            // 尝试从缓存获取数据
            $cachedData = Cache::get($cacheKey);
            if ($cachedData !== false) {
                Log::info('从缓存获取症状数据');
                return json(['code' => 0, 'data' => $cachedData, 'msg' => '']);
            }

            // 缓存未命中，从数据库获取数据
            Log::info('缓存未命中，从数据库获取症状数据');

            // 获取症状分类（包含所有语言字段）- 使用别名避免字段冲突
            $categories = Db::table('ai_symptom_categories')
                ->where('is_active', 1)
                ->field('id as category_id, name_zh_cn as cat_name_zh_cn, name_en_us as cat_name_en_us, name_ja_jp as cat_name_ja_jp, name_be_by as cat_name_be_by, sort_order as cat_sort_order')
                ->order('sort_order asc, id asc')
                ->select();

            $result = [];

            foreach ($categories as $category) {
                // 获取该分类下的症状（包含所有语言字段）
                $symptoms = Db::table('ai_symptoms')
                    ->where([
                        'category_id' => $category['category_id'],
                        'is_active' => 1
                    ])
                    ->field('id as symptom_id, name_zh_cn, name_en_us, name_ja_jp, name_be_by, sort_order')
                    ->order('sort_order asc, id asc')
                    ->select();

                // 格式化症状数据，保持与前端期望的数据结构一致
                $childSymptoms = [];
                foreach ($symptoms as $symptom) {
                    $childSymptoms[] = [
                        'value' => $symptom['symptom_id'], // 症状ID作为value
                        'name_zh_cn' => $symptom['name_zh_cn'],
                        'name_en_us' => $symptom['name_en_us'],
                        'name_ja_jp' => $symptom['name_ja_jp'],
                        'name_be_by' => $symptom['name_be_by']
                    ];
                }

                $result[] = [
                    'name_zh_cn' => $category['cat_name_zh_cn'],
                    'name_en_us' => $category['cat_name_en_us'],
                    'name_ja_jp' => $category['cat_name_ja_jp'],
                    'name_be_by' => $category['cat_name_be_by'],
                    'child' => $childSymptoms
                ];
            }

            // 将结果存入缓存，过期时间12小时（43200秒）
            Cache::set($cacheKey, $result, 43200);
            Log::info('症状数据已缓存，过期时间：12小时');

            return json(['code' => 0, 'data' => $result, 'msg' => '']);

        } catch (\Exception $e) {
            Log::error('获取症状列表失败: ' . $e->getMessage());
            return json(['code' => 1, 'data' => [], 'msg' => '获取症状列表失败']);
        }
    }

    /**
     * 清除症状缓存（用于数据更新后手动清理缓存）
     */
    public function clearSymptomsCache()
    {
        try {
            $cacheKey = 'symptoms_data_v1';
            $result = Cache::rm($cacheKey);

            if ($result) {
                Log::info('症状缓存清理成功');
                return json(['code' => 0, 'msg' => '缓存清理成功', 'data' => []]);
            } else {
                Log::warning('症状缓存清理失败或缓存不存在');
                return json(['code' => 0, 'msg' => '缓存不存在或已过期', 'data' => []]);
            }
        } catch (\Exception $e) {
            Log::error('清理症状缓存失败: ' . $e->getMessage());
            return json(['code' => 1, 'msg' => '清理缓存失败', 'data' => []]);
        }
    }

    /**
     * 获取缓存信息（用于调试）
     */
    public function getCacheInfo()
    {
        try {
            $cacheKey = 'symptoms_data_v1';
            $cachedData = Cache::get($cacheKey);

            if ($cachedData !== false) {
                return json([
                    'code' => 0,
                    'msg' => '缓存存在',
                    'data' => [
                        'cache_exists' => true,
                        'data_count' => count($cachedData),
                        'cache_key' => $cacheKey
                    ]
                ]);
            } else {
                return json([
                    'code' => 0,
                    'msg' => '缓存不存在',
                    'data' => [
                        'cache_exists' => false,
                        'cache_key' => $cacheKey
                    ]
                ]);
            }
        } catch (\Exception $e) {
            Log::error('获取缓存信息失败: ' . $e->getMessage());
            return json(['code' => 1, 'msg' => '获取缓存信息失败', 'data' => []]);
        }
    }

    /**
     * 将症状排列组合,输出所有排列.
     */
    private function _generateCombinations($input)
    {
        // 将输入字符串按逗号分割
        $symptoms = explode(',', $input);
        $result = [];

        // 生成组合的递归函数
        function combine($elements, $start, $current, &$result)
        {
            // 将当前组合添加到结果中
            if (!empty($current)) {
                $result[] = implode(',', $current);
            }

            // 递归生成组合
            for ($i = $start; $i < count($elements); ++$i) {
                // 包含当前元素
                $current[] = trim($elements[$i]);
                combine($elements, $i + 1, $current, $result);
                // 不包含当前元素
                array_pop($current);
            }
        }

        combine($symptoms, 0, [], $result);

        return $result;
    }

    /**
     * 生成所有可能的症状组合和排列（生成器版本）.
     */
    private function generateSymptomCombinations($input)
    {
        // 将输入字符串按逗号分割
        $symptoms = array_map('trim', explode(',', $input));
        $seen = []; // 用于去重

        // 生成所有可能的组合（从1个症状到全部症状）
        $totalSymptoms = count($symptoms);

        for ($length = 1; $length <= $totalSymptoms; ++$length) {
            // 生成指定长度的所有组合
            foreach ($this->getCombinations($symptoms, $length) as $combination) {
                // 对每个组合生成所有可能的排列
                foreach ($this->getPermutations($combination) as $permutation) {
                    $combinationStr = implode(',', $permutation);
                    // 去重处理
                    if (!isset($seen[$combinationStr])) {
                        $seen[$combinationStr] = true;
                        yield $combinationStr;
                    }
                }
            }
        }
    }

    /**
     * 生成指定长度的组合（生成器版本）.
     */
    private function getCombinations($array, $length)
    {
        if ($length == 1) {
            foreach ($array as $item) {
                yield [$item];
            }

            return;
        }

        $arrayLength = count($array);

        for ($i = 0; $i <= $arrayLength - $length; ++$i) {
            $firstElement = $array[$i];
            $remainingElements = array_slice($array, $i + 1);

            foreach ($this->getCombinations($remainingElements, $length - 1) as $subCombination) {
                yield array_merge([$firstElement], $subCombination);
            }
        }
    }

    /**
     * 生成数组的所有排列（生成器版本）.
     */
    private function getPermutations($array)
    {
        if (count($array) <= 1) {
            yield $array;

            return;
        }

        for ($i = 0; $i < count($array); ++$i) {
            $current = $array[$i];
            $remaining = array_merge(
                array_slice($array, 0, $i),
                array_slice($array, $i + 1)
            );

            foreach ($this->getPermutations($remaining) as $subPermutation) {
                yield array_merge([$current], $subPermutation);
            }
        }
    }

    public function index()
    {
        $userId = getUserId();
        $returnData = [];

        $symptomDesc = $params['symptom_desc'] ?? input('symptom_desc');

        if (!empty($symptomDesc)) {
            // 使用症状ID进行新的汤药匹配
            $returnData = $this->findFormulasBySymptomIds(trim($symptomDesc, '"'));
        }

        //减少免费次数
        $user = Db::name('member')->where('id', $userId)->find();
        if ($user['free_nums'] > 0) {
            Db::name('member')->where('id', $userId)->setDec('free_nums');
        } elseif ($user['symptom_inquiry_once_mums'] > 0) {
            Db::name('member')->where('id', $userId)->setDec('symptom_inquiry_once_mums');
        }

        // 保存历史记录（只保存症状ID，不保存结果，避免数据过长）
        if (!empty($symptomDesc)) {
            // 获取症状名称用于显示
            $symptomIds = array_map('trim', explode(',', $symptomDesc));
            $symptomNames = [];
            
            if (!empty($symptomIds)) {
                $symptoms = Db::table('ai_symptoms')
                    ->whereIn('id', $symptomIds)
                    ->field('name_zh_cn')
                    ->select();
                
                foreach ($symptoms as $symptom) {
                    if (!empty($symptom['name_zh_cn'])) {
                        $symptomNames[] = $symptom['name_zh_cn'];
                    }
                }
            }
            
            // 构建标题：症状查询 - 症状名称
            $title = Lang::get('symptom_query');
            if (!empty($symptomNames)) {
                $title .= ' - ' . implode('、', $symptomNames);
            }
            
            $historyData = [
                'user_phone' => (string) $userId,
                'person_menu_id' => 98, // 症状查询专用ID
                'person_menu_item_id' => 1,
                'person_fix_msg' => $title, // 症状查询 + 症状名称
                'result_id' => 0,
                'is_show_time_res' => 0,
                'add_time' => date('Y-m-d H:i:s', time()),
                'result_type' => 98, // 症状查询类型
                'question_id' => 98,
                'ta_result_id' => 0,
                'contact' => 0,
                'date1' => $symptomDesc, // 保存症状ID，用于重新查询
                'result_day' => '',
                'rate' => 0,
                'me_name' => '',
                'ta_name' => '',
                'input_data' => json_encode([
                    'symptom_desc' => $symptomDesc
                ], JSON_UNESCAPED_UNICODE),
            ];
            Db::name('new_user_question_history')->insert($historyData);
        }

        $res = ['code' => 0, 'data' => $returnData, 'msg' => ''];

        return Json($res);
    }

    /**
     * 根据症状ID查找匹配的汤药
     */
    public function findFormulasBySymptomIds($symptomIds)
    {
        try {
            // 将症状ID字符串转换为数组
            $inputSymptomIds = array_map('trim', explode(',', $symptomIds));
            $returnData = [];

            // 获取所有汤药数据
            $formulas = Db::table('ai_formulas')
                ->where('is_active', 1)
                ->field('id, name_zh_cn, name_en_us, name_ja_jp, name_be_by, main_symptom_ids, side_symptom_ids, ingredients_zh_cn, ingredients_en_us, ingredients_ja_jp, ingredients_be_by')
                ->select();

            if (empty($formulas)) {
                return $returnData;
            }

            // 生成症状组合进行匹配
            $symptomCombinations = $this->generateSymptomIdCombinations($inputSymptomIds);

            foreach ($formulas as $formula) {
                $matched = false;

                // 检查主症匹配
                if (!empty($formula['main_symptom_ids'])) {
                    $mainSymptomIds = array_map('trim', explode(',', $formula['main_symptom_ids']));

                    foreach ($symptomCombinations as $combination) {
                        // 检查组合是否完全匹配主症
                        if ($this->isExactMatch($combination, $mainSymptomIds)) {
                            $key = implode(',', $combination);
                            $returnData[$key] = $this->formatFormulaResult($formula, $combination);
                            $matched = true;
                            break;
                        }
                    }
                }

                // 如果主症没有匹配，检查兼症匹配
                if (!$matched && !empty($formula['side_symptom_ids'])) {
                    $sideSymptomIds = array_map('trim', explode(',', $formula['side_symptom_ids']));

                    foreach ($symptomCombinations as $combination) {
                        // 检查组合是否包含在兼症中
                        if ($this->isSubsetMatch($combination, $sideSymptomIds)) {
                            $key = implode(',', $combination);
                            $returnData[$key] = $this->formatFormulaResult($formula, $combination);
                            break;
                        }
                    }
                }
            }

            return $returnData;

        } catch (\Exception $e) {
            Log::error('症状匹配失败: ' . $e->getMessage());
            return [];
        }
    }

    /**
     * 生成症状ID的所有组合
     */
    private function generateSymptomIdCombinations($symptomIds)
    {
        $combinations = [];
        $totalSymptoms = count($symptomIds);

        // 生成所有可能的组合（从1个症状到全部症状）
        for ($length = 1; $length <= $totalSymptoms; $length++) {
            $combinations = array_merge($combinations, $this->getCombinationsArray($symptomIds, $length));
        }

        return $combinations;
    }

    /**
     * 生成指定长度的组合数组
     */
    private function getCombinationsArray($array, $length)
    {
        if ($length == 1) {
            return array_map(function($item) { return [$item]; }, $array);
        }

        $combinations = [];
        $arrayLength = count($array);

        for ($i = 0; $i <= $arrayLength - $length; $i++) {
            $firstElement = $array[$i];
            $remainingElements = array_slice($array, $i + 1);

            foreach ($this->getCombinationsArray($remainingElements, $length - 1) as $subCombination) {
                $combinations[] = array_merge([$firstElement], $subCombination);
            }
        }

        return $combinations;
    }

    /**
     * 检查是否完全匹配（用于主症）
     */
    private function isExactMatch($combination, $targetIds)
    {
        sort($combination);
        sort($targetIds);
        return $combination === $targetIds;
    }

    /**
     * 检查是否为子集匹配（用于兼症）
     */
    private function isSubsetMatch($combination, $targetIds)
    {
        foreach ($combination as $symptomId) {
            if (!in_array($symptomId, $targetIds)) {
                return false;
            }
        }
        return true;
    }

    /**
     * 格式化汤药结果
     */
    private function formatFormulaResult($formula, $combination)
    {
        // 直接返回所有语言字段，让前端决定使用哪个语言
        return [
            'name_zh_cn' => $formula['name_zh_cn'],
            'name_en_us' => $formula['name_en_us'],
            'name_ja_jp' => $formula['name_ja_jp'],
            'name_be_by' => $formula['name_be_by'],
            'ingredients_zh_cn' => $formula['ingredients_zh_cn'],
            'ingredients_en_us' => $formula['ingredients_en_us'],
            'ingredients_ja_jp' => $formula['ingredients_ja_jp'],
            'ingredients_be_by' => $formula['ingredients_be_by']
        ];
    }



    //在这里处理目前分类和当前症状多语言的返回  处理成sql文件  我需要更新到现有结果中去
    public function getInsertSql(){
        try {
            // 1. 加载多语言文件
            $languages = $this->loadLanguageFiles();

            // 2. 获取旧数据
            $treatiseService = new TreatiseService();
            $oldData = $treatiseService->getTreatiseList();

            // 3. 生成完整SQL
            $sql = $this->generateCompleteSql($oldData, $languages);

            // 4. 输出SQL文件
            $this->outputSqlFile($sql);

            return json(['code' => 0, 'msg' => 'SQL文件生成成功', 'data' => []]);

        } catch (\Exception $e) {
            Log::error('生成SQL文件失败: ' . $e->getMessage());
            return json(['code' => 1, 'msg' => '生成SQL文件失败: ' . $e->getMessage(), 'data' => []]);
        }
    }

    /**
     * 加载多语言文件
     */
    private function loadLanguageFiles()
    {
        $languages = [
            'zh_cn' => [],
            'en_us' => [],
            'ja_jp' => [],
            'be_by' => []
        ];

        $langPaths = [
            'zh_cn' => __DIR__ . '/../../lang/zh-cn/common.php',
            'en_us' => __DIR__ . '/../../lang/en-us/common.php',
            'ja_jp' => __DIR__ . '/../../lang/ja-jp/common.php',
            'be_by' => __DIR__ . '/../../lang/be-by/common.php'
        ];

        foreach ($langPaths as $lang => $path) {
            if (file_exists($path)) {
                $languages[$lang] = include $path;
            }
        }

        return $languages;
    }

    /**
     * 生成完整的SQL文件内容
     */
    private function generateCompleteSql($oldData, $languages)
    {
        $sql = "-- 症状分类和症状多语言数据迁移SQL\n";
        $sql .= "-- 生成时间: " . date('Y-m-d H:i:s') . "\n\n";

        // 删除旧表
        $sql .= "-- 删除现有表\n";
        $sql .= "DROP TABLE IF EXISTS `ai_symptoms`;\n";
        $sql .= "DROP TABLE IF EXISTS `ai_symptom_categories`;\n\n";

        // 创建新表
        $sql .= $this->getCreateTableSql();

        // 生成分类数据
        $sql .= "-- 插入症状分类数据\n";
        $categorySql = $this->generateCategorySql($oldData, $languages);
        $sql .= $categorySql;

        // 生成症状数据
        $sql .= "\n-- 插入症状数据\n";
        $symptomSql = $this->generateSymptomSql($oldData, $languages);
        $sql .= $symptomSql;

        return $sql;
    }

    /**
     * 获取创建表的SQL
     */
    private function getCreateTableSql()
    {
        return "-- 创建症状分类表
CREATE TABLE `ai_symptom_categories` (
    `id` INT PRIMARY KEY AUTO_INCREMENT,
    `name_zh_cn` VARCHAR(100) NOT NULL COMMENT '中文名称',
    `name_en_us` VARCHAR(100) NOT NULL COMMENT '英文名称',
    `name_ja_jp` VARCHAR(100) NOT NULL COMMENT '日文名称',
    `name_be_by` VARCHAR(100) NOT NULL COMMENT '白俄罗斯语名称',
    `description_zh_cn` TEXT COMMENT '中文描述',
    `description_en_us` TEXT COMMENT '英文描述',
    `description_ja_jp` TEXT COMMENT '日文描述',
    `description_be_by` TEXT COMMENT '白俄罗斯语描述',
    `sort_order` INT DEFAULT 0 COMMENT '排序',
    `is_active` BOOLEAN DEFAULT TRUE COMMENT '是否启用',
    `created_at` TIMESTAMP DEFAULT CURRENT_TIMESTAMP,
    `updated_at` TIMESTAMP DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP,
    INDEX idx_sort_order (sort_order),
    INDEX idx_is_active (is_active)
) COMMENT='症状分类表';

-- 创建症状表
CREATE TABLE `ai_symptoms` (
    `id` INT PRIMARY KEY AUTO_INCREMENT,
    `category_id` INT NOT NULL COMMENT '所属分类ID',
    `name_zh_cn` VARCHAR(200) NOT NULL COMMENT '中文名称',
    `name_en_us` VARCHAR(200) NOT NULL COMMENT '英文名称',
    `name_ja_jp` VARCHAR(200) NOT NULL COMMENT '日文名称',
    `name_be_by` VARCHAR(200) NOT NULL COMMENT '白俄罗斯语名称',
    `description_zh_cn` TEXT COMMENT '中文描述',
    `description_en_us` TEXT COMMENT '英文描述',
    `description_ja_jp` TEXT COMMENT '日文描述',
    `description_be_by` TEXT COMMENT '白俄罗斯语描述',
    `keywords_zh_cn` TEXT COMMENT '中文关键词，逗号分隔',
    `keywords_en_us` TEXT COMMENT '英文关键词，逗号分隔',
    `keywords_ja_jp` TEXT COMMENT '日文关键词，逗号分隔',
    `keywords_be_by` TEXT COMMENT '白俄罗斯语关键词，逗号分隔',
    `sort_order` INT DEFAULT 0 COMMENT '排序',
    `is_active` BOOLEAN DEFAULT TRUE COMMENT '是否启用',
    `created_at` TIMESTAMP DEFAULT CURRENT_TIMESTAMP,
    `updated_at` TIMESTAMP DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP,
    INDEX idx_category_id (category_id),
    INDEX idx_sort_order (sort_order),
    INDEX idx_is_active (is_active),
    FULLTEXT idx_search_zh_cn (name_zh_cn, keywords_zh_cn),
    FULLTEXT idx_search_en_us (name_en_us, keywords_en_us),
    FULLTEXT idx_search_ja_jp (name_ja_jp, keywords_ja_jp),
    FULLTEXT idx_search_be_by (name_be_by, keywords_be_by),
    FOREIGN KEY (category_id) REFERENCES ai_symptom_categories(id) ON DELETE CASCADE
) COMMENT='症状表';

";
    }

    /**
     * 生成分类插入SQL
     */
    private function generateCategorySql($oldData, $languages)
    {
        $sql = "INSERT INTO `ai_symptom_categories`
(name_zh_cn, name_en_us, name_ja_jp, name_be_by, sort_order, is_active) VALUES\n";

        $categoryValues = [];
        $sortOrder = 1;

        foreach ($oldData as $category) {
            $categoryKey = $category['name'];

            // 获取各语言翻译
            $nameZhCn = $this->getTranslation($languages['zh_cn'], $categoryKey, $categoryKey);
            $nameEnUs = $this->getTranslation($languages['en_us'], $categoryKey, $categoryKey);
            $nameJaJp = $this->getTranslation($languages['ja_jp'], $categoryKey, $categoryKey);
            $nameBeBy = $this->getTranslation($languages['be_by'], $categoryKey, $categoryKey);

            $categoryValues[] = sprintf(
                "('%s', '%s', '%s', '%s', %d, 1)",
                addslashes($nameZhCn),
                addslashes($nameEnUs),
                addslashes($nameJaJp),
                addslashes($nameBeBy),
                $sortOrder++
            );
        }

        $sql .= implode(",\n", $categoryValues) . ";\n";

        return $sql;
    }

    /**
     * 生成症状插入SQL
     */
    private function generateSymptomSql($oldData, $languages)
    {
        $sql = "INSERT INTO `ai_symptoms`
(category_id, name_zh_cn, name_en_us, name_ja_jp, name_be_by, sort_order, is_active) VALUES\n";

        $symptomValues = [];
        $categoryId = 1;

        foreach ($oldData as $category) {
            $sortOrder = 1;

            if (isset($category['child']) && is_array($category['child'])) {
                foreach ($category['child'] as $symptom) {
                    $symptomKey = $symptom['name'];

                    // 获取各语言翻译
                    $nameZhCn = $this->getTranslation($languages['zh_cn'], $symptomKey, $symptomKey);
                    $nameEnUs = $this->getTranslation($languages['en_us'], $symptomKey, $symptomKey);
                    $nameJaJp = $this->getTranslation($languages['ja_jp'], $symptomKey, $symptomKey);
                    $nameBeBy = $this->getTranslation($languages['be_by'], $symptomKey, $symptomKey);

                    $symptomValues[] = sprintf(
                        "(%d, '%s', '%s', '%s', '%s', %d, 1)",
                        $categoryId,
                        addslashes($nameZhCn),
                        addslashes($nameEnUs),
                        addslashes($nameJaJp),
                        addslashes($nameBeBy),
                        $sortOrder++
                    );
                }
            }

            $categoryId++;
        }

        $sql .= implode(",\n", $symptomValues) . ";\n";

        return $sql;
    }

    /**
     * 获取翻译文本
     */
    private function getTranslation($langArray, $key, $default = '')
    {
        return isset($langArray[$key]) ? $langArray[$key] : $default;
    }

    /**
     * 输出SQL文件
     */
    private function outputSqlFile($sql)
    {
        $filename = 'symptom_migration_' . date('Y-m-d_H-i-s') . '.sql';

        // 设置下载头
        header('Content-Type: application/octet-stream');
        header('Content-Disposition: attachment; filename="' . $filename . '"');
        header('Content-Length: ' . strlen($sql));

        echo $sql;
        exit;
    }



    //输出汤药对应的sql文件  这块可能比较复杂
    public function getFormulaSql(){
        try {
            // 1. 加载多语言文件
            $languages = $this->loadLanguageFiles();

            // 2. 获取汤药映射数据
            $treatise = new Treatise();
            $formulaMapMain = $treatise->formula_map_main;
            $formulaMapSide = $treatise->formula_map_side;

            // 3. 建立症状名称到ID的映射
            $symptomNameToIdMap = $this->buildSymptomNameToIdMap($languages);

            // 4. 建立汤药编号到多语言名称的映射
            $formulaCodeToNamesMap = $this->buildFormulaCodeToNamesMap($languages);

            // 5. 生成完整的汤药SQL
            $sql = $this->generateFormulaSql($formulaMapMain, $formulaMapSide, $symptomNameToIdMap, $formulaCodeToNamesMap);

            // 6. 输出SQL文件
            $this->outputFormulaSqlFile($sql);

            return json(['code' => 0, 'msg' => '汤药SQL文件生成成功', 'data' => []]);

        } catch (\Exception $e) {
            // 记录详细错误信息以便调试
            $errorMsg = '生成汤药SQL文件失败: ' . $e->getMessage() . ' 文件: ' . $e->getFile() . ' 行号: ' . $e->getLine();
            error_log($errorMsg);
            return json(['code' => 1, 'msg' => '生成汤药SQL文件失败: ' . $e->getMessage(), 'data' => []]);
        }
    }

    /**
     * 建立症状名称到ID的映射
     */
    private function buildSymptomNameToIdMap($languages)
    {
        $nameToIdMap = [];

        // 获取所有症状数据（假设症状表已存在）
        try {
            $symptoms = Db::table('ai_symptoms')
                ->field('id, name_zh_cn, name_en_us, name_ja_jp, name_be_by')
                ->select();

            if (!empty($symptoms)) {
                error_log("从数据库找到 " . count($symptoms) . " 个症状记录");

                // 首先建立数据库中多语言名称到ID的映射
                $dbNameToIdMap = [];
                foreach ($symptoms as $symptom) {
                    foreach (['zh_cn', 'en_us', 'ja_jp', 'be_by'] as $lang) {
                        $name = $symptom["name_{$lang}"];
                        if (!empty($name)) {
                            $dbNameToIdMap[$name] = $symptom['id'];
                        }
                    }
                }

                // 然后建立多语言文件key到数据库ID的映射
                // 这是为了让汤药映射使用的英文key能找到对应的数据库ID
                foreach ($languages as $langCode => $langData) {
                    if (!is_array($langData)) continue;

                    foreach ($langData as $key => $value) {
                        // 跳过汤药相关的key
                        if (strpos($key, 'formula_') === 0) {
                            continue;
                        }

                        // 如果多语言文件的value在数据库中找到了对应的ID
                        if (isset($dbNameToIdMap[$value])) {
                            $nameToIdMap[$key] = $dbNameToIdMap[$value]; // key -> 数据库ID
                            if (!isset($nameToIdMap[$value])) {
                                $nameToIdMap[$value] = $dbNameToIdMap[$value]; // value -> 数据库ID
                            }
                        }
                    }
                }

                error_log("从数据库建立了 " . count($nameToIdMap) . " 个症状名称映射");

                // 检查关键症状是否在数据库映射中
                $testKeys = ['headache', 'low_grade_fever', 'sweating_not_due_to_heat_or_activity', 'fear_of_wind'];
                foreach ($testKeys as $key) {
                    if (isset($nameToIdMap[$key])) {
                        error_log("数据库映射中找到: {$key} -> ID {$nameToIdMap[$key]}");
                    } else {
                        error_log("数据库映射中未找到: {$key}");
                    }
                }

                return $nameToIdMap;
            }
        } catch (\Exception $e) {
            Log::error('症状表不存在或查询失败，使用多语言文件建立映射: ' . $e->getMessage());
        }

        // 如果症状表不存在或为空，通过多语言文件建立临时映射
        error_log("使用多语言文件建立症状名称映射");
        $tempId = 1;
        $processedKeys = []; // 记录已处理的键名，避免重复

        // 先处理所有key（英文症状名），这些是汤药映射中使用的
        foreach ($languages as $langCode => $langData) {
            if (!is_array($langData)) continue;

            foreach ($langData as $key => $value) {
                // 跳过汤药相关的key
                if (strpos($key, 'formula_') === 0) {
                    continue;
                }

                // 处理key（英文症状名）
                if (!isset($processedKeys[$key])) {
                    $nameToIdMap[$key] = $tempId;
                    $processedKeys[$key] = true;

                    // 同时为所有语言的翻译建立映射
                    foreach ($languages as $otherLangCode => $otherLangData) {
                        if (is_array($otherLangData) && isset($otherLangData[$key]) && !isset($nameToIdMap[$otherLangData[$key]])) {
                            $nameToIdMap[$otherLangData[$key]] = $tempId;
                        }
                    }

                    $tempId++;
                }
            }
        }

        error_log("通过多语言文件建立了 " . count($nameToIdMap) . " 个症状名称映射");

        // 检查关键症状是否在多语言映射中
        $testKeys = ['headache', 'low_grade_fever', 'sweating_not_due_to_heat_or_activity', 'fear_of_wind'];
        foreach ($testKeys as $key) {
            if (isset($nameToIdMap[$key])) {
                error_log("多语言映射中找到: {$key} -> ID {$nameToIdMap[$key]}");
            } else {
                error_log("多语言映射中未找到: {$key}");
            }
        }

        return $nameToIdMap;
    }

    /**
     * 建立汤药编号到多语言名称的映射
     */
    private function buildFormulaCodeToNamesMap($languages)
    {
        $formulaMap = [];

        // 遍历所有语言文件，查找汤药定义
        foreach ($languages as $langCode => $langData) {
            foreach ($langData as $key => $value) {
                if (strpos($key, 'formula_') === 0) {
                    if (!isset($formulaMap[$key])) {
                        $formulaMap[$key] = [
                            'name_zh_cn' => '',
                            'name_en_us' => '',
                            'name_ja_jp' => '',
                            'name_be_by' => ''
                        ];
                    }
                    $formulaMap[$key]["name_{$langCode}"] = $value;
                }
            }
        }

        return $formulaMap;
    }

    /**
     * 将症状名称组合转换为症状ID组合
     */
    private function convertSymptomNamesToIds($symptomNames, $nameToIdMap)
    {
        $symptomIds = [];
        $symptomArray = array_map('trim', explode(',', $symptomNames));

        foreach ($symptomArray as $symptomName) {
            if (isset($nameToIdMap[$symptomName])) {
                $symptomIds[] = $nameToIdMap[$symptomName];
            } else {
                // 如果找不到对应ID，记录日志但继续处理
                Log::warning("症状名称未找到对应ID: {$symptomName}");
            }
        }

        return implode(',', array_unique($symptomIds));
    }

    /**
     * 生成汤药SQL
     */
    private function generateFormulaSql($formulaMapMain, $formulaMapSide, $symptomNameToIdMap, $formulaCodeToNamesMap)
    {
        $sql = "-- 汤药多语言数据迁移SQL\n";
        $sql .= "-- 生成时间: " . date('Y-m-d H:i:s') . "\n\n";

        // 删除旧表
        $sql .= "-- 删除现有汤药表\n";
        $sql .= "DROP TABLE IF EXISTS `ai_formulas`;\n\n";

        // 创建汤药表
        $sql .= $this->getFormulaCreateTableSql();

        // 收集所有汤药数据
        $formulaData = [];

        // 处理主症数据
        foreach ($formulaMapMain as $symptomNames => $formulaCode) {
            if (!isset($formulaData[$formulaCode])) {
                $formulaData[$formulaCode] = [
                    'main_symptom_ids' => [],
                    'side_symptom_ids' => []
                ];
            }

            $symptomIds = $this->convertSymptomNamesToIds($symptomNames, $symptomNameToIdMap);
            // 调试信息
            error_log("主症处理: {$symptomNames} -> {$formulaCode}, 转换ID: '{$symptomIds}'");

            if (!empty($symptomIds)) {
                // 拆分逗号分隔的ID字符串，添加到数组中
                $idsArray = array_map('trim', explode(',', $symptomIds));
                error_log("主症ID数组: " . json_encode($idsArray));

                $formulaData[$formulaCode]['main_symptom_ids'] = array_merge(
                    $formulaData[$formulaCode]['main_symptom_ids'],
                    $idsArray
                );
            }
        }

        // 处理兼症数据
        foreach ($formulaMapSide as $symptomNames => $formulaCode) {
            if (!isset($formulaData[$formulaCode])) {
                $formulaData[$formulaCode] = [
                    'main_symptom_ids' => [],
                    'side_symptom_ids' => []
                ];
            }

            $symptomIds = $this->convertSymptomNamesToIds($symptomNames, $symptomNameToIdMap);
            // 调试信息
            error_log("兼症处理: {$symptomNames} -> {$formulaCode}, 转换ID: '{$symptomIds}'");

            if (!empty($symptomIds)) {
                // 拆分逗号分隔的ID字符串，添加到数组中
                $idsArray = array_map('trim', explode(',', $symptomIds));
                error_log("兼症ID数组: " . json_encode($idsArray));

                $formulaData[$formulaCode]['side_symptom_ids'] = array_merge(
                    $formulaData[$formulaCode]['side_symptom_ids'],
                    $idsArray
                );
            }
        }

        // 生成INSERT语句
        $sql .= "\n-- 插入汤药数据\n";
        $sql .= $this->generateFormulaInsertSql($formulaData, $formulaCodeToNamesMap);

        return $sql;
    }

    /**
     * 获取汤药表创建SQL
     */
    private function getFormulaCreateTableSql()
    {
        return "-- 创建汤药表
CREATE TABLE `ai_formulas` (
    `id` INT PRIMARY KEY AUTO_INCREMENT,
    `name_zh_cn` VARCHAR(200) NOT NULL COMMENT '中文名称',
    `name_en_us` VARCHAR(200) NOT NULL COMMENT '英文名称',
    `name_ja_jp` VARCHAR(200) NOT NULL COMMENT '日文名称',
    `name_be_by` VARCHAR(200) NOT NULL COMMENT '白俄罗斯语名称',
    `description_zh_cn` TEXT COMMENT '中文描述',
    `description_en_us` TEXT COMMENT '英文描述',
    `description_ja_jp` TEXT COMMENT '日文描述',
    `description_be_by` TEXT COMMENT '白俄罗斯语描述',
    `ingredients_zh_cn` TEXT COMMENT '中文成分配方',
    `ingredients_en_us` TEXT COMMENT '英文成分配方',
    `ingredients_ja_jp` TEXT COMMENT '日文成分配方',
    `ingredients_be_by` TEXT COMMENT '白俄罗斯语成分配方',
    `usage_method_zh_cn` TEXT COMMENT '中文使用方法',
    `usage_method_en_us` TEXT COMMENT '英文使用方法',
    `usage_method_ja_jp` TEXT COMMENT '日文使用方法',
    `usage_method_be_by` TEXT COMMENT '白俄罗斯语使用方法',
    `effects_zh_cn` TEXT COMMENT '中文功效作用',
    `effects_en_us` TEXT COMMENT '英文功效作用',
    `effects_ja_jp` TEXT COMMENT '日文功效作用',
    `effects_be_by` TEXT COMMENT '白俄罗斯语功效作用',
    `precautions_zh_cn` TEXT COMMENT '中文注意事项',
    `precautions_en_us` TEXT COMMENT '英文注意事项',
    `precautions_ja_jp` TEXT COMMENT '日文注意事项',
    `precautions_be_by` TEXT COMMENT '白俄罗斯语注意事项',
    `main_symptom_ids` TEXT COMMENT '主症ID列表，逗号分隔',
    `side_symptom_ids` TEXT COMMENT '兼症ID列表，逗号分隔',
    `sort_order` INT DEFAULT 0 COMMENT '排序',
    `is_active` BOOLEAN DEFAULT TRUE COMMENT '是否启用',
    `created_at` TIMESTAMP DEFAULT CURRENT_TIMESTAMP,
    `updated_at` TIMESTAMP DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP,
    INDEX idx_sort_order (sort_order),
    INDEX idx_is_active (is_active)
) COMMENT='汤药表';

";
    }

    /**
     * 生成汤药插入SQL
     */
    private function generateFormulaInsertSql($formulaData, $formulaCodeToNamesMap)
    {
        $sql = "INSERT INTO `ai_formulas`
(name_zh_cn, name_en_us, name_ja_jp, name_be_by, main_symptom_ids, side_symptom_ids, is_active) VALUES\n";

        $formulaValues = [];

        foreach ($formulaData as $formulaCode => $data) {
            // 获取汤药名称
            $formulaNames = $formulaCodeToNamesMap[$formulaCode] ?? [
                'name_zh_cn' => $formulaCode,
                'name_en_us' => $formulaCode,
                'name_ja_jp' => $formulaCode,
                'name_be_by' => $formulaCode
            ];

            // 合并同一汤药的多个症状组合
            $mainSymptomIds = implode(',', array_unique(array_filter($data['main_symptom_ids'])));
            $sideSymptomIds = implode(',', array_unique(array_filter($data['side_symptom_ids'])));

            // 调试信息 - 可以在生产环境中删除
            error_log("汤药 {$formulaCode}: 主症ID数组=" . json_encode($data['main_symptom_ids']) . ", 兼症ID数组=" . json_encode($data['side_symptom_ids']));
            error_log("汤药 {$formulaCode}: 最终主症ID='{$mainSymptomIds}', 最终兼症ID='{$sideSymptomIds}'");

            $formulaValues[] = sprintf(
                "('%s', '%s', '%s', '%s', '%s', '%s', 1)",
                addslashes($formulaNames['name_zh_cn']),
                addslashes($formulaNames['name_en_us']),
                addslashes($formulaNames['name_ja_jp']),
                addslashes($formulaNames['name_be_by']),
                $mainSymptomIds,
                $sideSymptomIds
            );
        }

        $sql .= implode(",\n", $formulaValues) . ";\n";

        return $sql;
    }

    /**
     * 输出汤药SQL文件
     */
    private function outputFormulaSqlFile($sql)
    {
        $filename = 'formula_migration_' . date('Y-m-d_H-i-s') . '.sql';

        // 设置下载头
        header('Content-Type: application/octet-stream');
        header('Content-Disposition: attachment; filename="' . $filename . '"');
        header('Content-Length: ' . strlen($sql));

        echo $sql;
        exit;
    }

    /**
     * 测试生成汤药SQL内容（用于调试）
     */
    public function testFormulaSql(){
        try {
            // 1. 加载多语言文件
            $languages = $this->loadLanguageFiles();

            // 2. 获取汤药映射数据
            $treatise = new Treatise();
            $formulaMapMain = $treatise->formula_map_main;
            $formulaMapSide = $treatise->formula_map_side;

            // 3. 建立症状名称到ID的映射
            $symptomNameToIdMap = $this->buildSymptomNameToIdMap($languages);

            // 4. 建立汤药编号到多语言名称的映射
            $formulaCodeToNamesMap = $this->buildFormulaCodeToNamesMap($languages);

            // 5. 生成完整的汤药SQL
            $sql = $this->generateFormulaSql($formulaMapMain, $formulaMapSide, $symptomNameToIdMap, $formulaCodeToNamesMap);

            // 6. 输出SQL内容到页面查看
            header('Content-Type: text/plain; charset=utf-8');
            echo $sql;
            exit;

        } catch (\Exception $e) {
            $errorMsg = '生成汤药SQL失败: ' . $e->getMessage() . ' 文件: ' . $e->getFile() . ' 行号: ' . $e->getLine();
            header('Content-Type: text/plain; charset=utf-8');
            echo $errorMsg;
            exit;
        }
    }
}
